package com.buer.storage.utils;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;



/**
 * 将数据集合导出到excel文件中，如果数据集合为空则生成只有标题的模板文件
 * @author Administrator
 *
 */
public class ExcelWriter {
	
	private OutputStream out;
	private XSSFWorkbook wb;
	
	/**
	 * 构造方法
	 * @param out 数据写入流
	 */
	public ExcelWriter(OutputStream out){
		this.out = out;
		wb = new XSSFWorkbook();
	}
	
	/**
	 * 写入数据集合
	 * @param list  数据集合
	 * @param clazz 需要转换的类
	 */
	public void writeList(List<?> list,Class<?> clazz) {
        try {
        	if(!clazz.isAnnotationPresent(TableMeta.class)){
				return;
			}
			
			TableMeta tableMeta = clazz.getAnnotation(TableMeta.class);
			
			//获得需要转换的字段
			List<Field> fieldList = new ArrayList<Field>();
			Field[] fields = clazz.getDeclaredFields();
			for(Field field : fields){
				if(field.isAnnotationPresent(FieldMeta.class)){
					fieldList.add(field);
				}
			}
			
			if(fieldList.isEmpty()){
				return;
			}
            
			XSSFSheet sheet = wb.createSheet(tableMeta.name());
            
            sheet.setDefaultColumnWidth((short) 30);//列的宽度
            
            //必填项表头字体
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            XSSFFont font = wb.createFont();
            font.setColor(IndexedColors.RED.getIndex());
            font.setBold(true);
            cellStyle.setFont(font);
            
            //非必填项表头字体
            XSSFCellStyle cellStyle2 = wb.createCellStyle();
            cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle2.setAlignment(HorizontalAlignment.CENTER);
            XSSFFont font2 = wb.createFont();
            font2.setBold(true);
            cellStyle2.setFont(font2);  
            
            //文本格式单元格
            XSSFCellStyle textCellStyle = wb.createCellStyle();
            XSSFDataFormat textDataFormat = wb.createDataFormat();
            textCellStyle.setDataFormat(textDataFormat.getFormat("@")); 
            
            XSSFRow titleRow = sheet.createRow(0); // 创建第一行，并在该行创建单元格，设置内容，做为标题行
            titleRow.setHeight((short)400);
            
            for(int i=0 ;i < fieldList.size();i++){
            	XSSFCell cell = titleRow.createCell(i);
            	Field field = fieldList.get(i);
            	FieldMeta fMeta = field.getAnnotation(FieldMeta.class);
            	cell.setCellValue(fMeta.name());
            	
            	if(fMeta.nullable()){
            		cell.setCellStyle(cellStyle2);//非必填项表头字体
            	}else{
            		cell.setCellStyle(cellStyle);//必填项表头字体
            	}

            	
            	//生成数据集合下拉框
            	if(CheckEmptyUtil.isNotEmpty(fMeta.dataArray())){
            		/*DVConstraint constraint = DVConstraint.createExplicitListConstraint(fMeta.dataArray());
		            CellRangeAddressList regions = new CellRangeAddressList(1, 10000, i,i);
		            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
		            sheet.addValidationData(dataValidation);*/
		            
		            DataValidationHelper helper = sheet.getDataValidationHelper();
		            DataValidationConstraint constraint = helper.createExplicitListConstraint(fMeta.dataArray());
		            CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, i,i);
		            DataValidation dataValidation = helper.createValidation(constraint, addressList);
		            sheet.addValidationData(dataValidation);
            	}
            }
            
            if(list != null && !list.isEmpty()){
            	int rowIndex = 1;
            	for(Object obj : list){
            		XSSFRow row = sheet.createRow(rowIndex);
            		for(int i=0 ;i < fieldList.size();i++){
            			XSSFCell cell = row.createCell(i);
                    	Field field = fieldList.get(i);
                    	field.setAccessible(true);
                    	
                    	Object value = field.get(obj);
                    	FieldMeta fMeta = field.getAnnotation(FieldMeta.class);
                    	
                    	//数据集合判断
                    	if(value != null && CheckEmptyUtil.isNotEmpty(fMeta.dataArray())){
                    		if(!ArrayUtils.contains(fMeta.dataArray(), value)){
                    			value = "";
                    		}
                    	}
                    	
                    	//日期转换
                    	if(value != null && value instanceof Date){
                    		if(isEmpty(fMeta.format())){
                    			value = "";
                    		}else{
                    			value = DateUtils.formatDate((Date)value, fMeta.format());
                    		}
                    	}
                    	
                    	cell.setCellStyle(textCellStyle); 
                    	if(value != null){
                    		cell.setCellValue(value.toString());
                    	}else{
                    		cell.setCellValue("");
                    	}
            		}
            		rowIndex++;
            	}
            }

            wb.write(out);
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	try {
        		if(wb != null){
        			wb.close();
            	}
        		if(out != null){
            		out.close();
            	}
			} catch (IOException e) {
				e.printStackTrace();
			}
        	
        }
    }
	
	private boolean isEmpty(String str){
		if(str == null || str.trim().equals("")){
			return true;
		}
		return false;
	}

}
